package uk.ac.cam.echo.server.integrationtests;

import static org.junit.Assert.*;

import java.sql.SQLException;
import java.sql.Statement;

import org.junit.Test;

import uk.ac.cam.echo.server.Database;

/**
 * @author Tom Smith
 */
public class TestAttractionTable extends TestDatabase {

	@Test
	/**
	 * Insert one stage without general location into the table.
	 */
	public void insertStageNoLocation() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.execute("INSERT INTO " + Database.tblAttraction + "(LocationRFID, AttractionName, AttractionType)" +
					" VALUES(NULL, 'Main Stage', 'Stage');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}

	@Test(expected=SQLException.class)
	/**
	 * Insert one stage with bad location into the table.
	 */
	public void insertStageWithBadLocation() throws SQLException {
		// Execute SQL statement.
		Statement sqlStatement = super.m_Connection.createStatement();
		sqlStatement.execute("INSERT INTO " + Database.tblAttraction + "(LocationRFID, AttractionName, AttractionType) VALUES('1', 'Main Stage', 'Stage');");
		sqlStatement.close();

		// Commit changes to file, and release DB locks.
		super.m_Connection.commit();

		fail("Location referenced does not exist.");
	}

	@Test
	/**
	 * Insert one stage with general location into the table.
	 */
	public void insertStageWithLocation() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.execute("INSERT INTO " + Database.tblLocation + "(LocationRFID, LocationName) VALUES('1', 'Chapel Court');");
			sqlStatement.execute("INSERT INTO " + Database.tblAttraction + "(LocationRFID, AttractionName, AttractionType) VALUES('1', 'Main Stage', 'Stage');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}

	@Test
	/**
	 * Insert multiple stages at multiple locations.
	 */
	public void multipleStages() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			
			// Create different locations.
			for (int i = 0; i < 10; i++) {
				sqlStatement.execute("INSERT INTO " + Database.tblLocation + "(LocationRFID, LocationName) " +
						"VALUES('" + i + "', 'Location" + i + "');");
			}
			
			// Insert ten stages at each location.
			for (int i = 0; i < 10; i++) {
				for (int j = 0; j < 10; j++) {
					sqlStatement.execute("INSERT INTO " + Database.tblAttraction + "(LocationRFID, AttractionName, AttractionType) " +
							"VALUES('" + j + "', 'Attraction" + i + "', 'Stage');");
				}
			}

			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}

	@Test
	/**
	 * Insert two stages in different locations with same name.
	 */
	public void twoStagesDifferentLocationSameName() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.execute("INSERT INTO " + Database.tblLocation + "(LocationRFID, LocationName) VALUES('1', 'Chapel Court');");
			sqlStatement.execute("INSERT INTO " + Database.tblLocation + "(LocationRFID, LocationName) VALUES('2', 'Garden');");
			sqlStatement.execute("INSERT INTO " + Database.tblAttraction + "(LocationRFID, AttractionName, AttractionType) VALUES('1', 'Main Stage', 'Stage');");
			sqlStatement.execute("INSERT INTO " + Database.tblAttraction + "(LocationRFID, AttractionName, AttractionType) VALUES('2', 'Main Stage', 'Stage');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}
	
	@Test
	/**
	 * Insert two stages in same location with different name.
	 */
	public void twoStagesSameLocationDifferentName() {
		try {
			// Execute SQL statement.
			Statement sqlStatement = super.m_Connection.createStatement();
			sqlStatement.execute("INSERT INTO " + Database.tblLocation + "(LocationRFID, LocationName) VALUES('1', 'Chapel Court');");
			sqlStatement.execute("INSERT INTO " + Database.tblAttraction + "(LocationRFID, AttractionName, AttractionType) VALUES('1', 'Main Stage', 'Stage');");
			sqlStatement.execute("INSERT INTO " + Database.tblAttraction + "(LocationRFID, AttractionName, AttractionType) VALUES('1', 'Burger Van', 'Food');");
			sqlStatement.close();

			// Commit changes to file, and release DB locks.
			super.m_Connection.commit();

		} catch (SQLException e) {
			fail("An SQL Exception occured - " + e.getMessage());
		}
	}

	@Test(expected=SQLException.class)
	/**
	 * Insert two stages in same location with same name.
	 */
	public void twoStagesSameLocationSameName() throws SQLException {
		// Execute SQL statement.
		Statement sqlStatement = super.m_Connection.createStatement();
		sqlStatement.execute("INSERT INTO " + Database.tblLocation + "(LocationRFID, LocationName) VALUES('1', 'Chapel Court');");
		sqlStatement.execute("INSERT INTO " + Database.tblAttraction + "(LocationRFID, AttractionName, AttractionType) VALUES('1', 'Main Stage', 'Stage');");
		sqlStatement.execute("INSERT INTO " + Database.tblAttraction + "(LocationRFID, AttractionName, AttractionType) VALUES('1', 'Main Stage', 'Stage');");
		sqlStatement.close();

		// Commit changes to file, and release DB locks.
		super.m_Connection.commit();

		fail("Same stage in this area is specified twice.");
	}
}
